package com.sjtu.streetplan.sqlite;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import com.sjtu.streetplan.util.Position;

public class SQLite { 
	private DatabaseHelper dbHelper;     
    
    private static final String DATABASE_NAME = "db.db";     
    private static final int DATABASE_VERSION = 1;     
    private static final String TABLE_NAME = "gps"; 
    private static final String TAG_SQL = "SQLite";
	 
    public SQLite(Context context) {
        
        //��ʼ����ݿ�������
        dbHelper = new DatabaseHelper(context, DATABASE_NAME, null,     
                DATABASE_VERSION);
    }  
     /**
     * ID INTEGER PRIMARY KEY
     * PHONE VARCHAR
     * TIME	 TIMESTAMP
     * X	 FLOAT
     * Y	 FLOAT
     * SPEED FLOAT
     */
    
    // ������ݱ�     
    public void createTable() {   
        SQLiteDatabase db = dbHelper.getWritableDatabase();     
        String sql = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME     
                + " (ID INTEGER PRIMARY KEY, PHONE VARCHAR, TIME TIMESTAMP, LON FLOAT, LAT FLOAT, ANGLE FLOAT);";     
        try {     
            db.execSQL(sql);     
			Log.i(TAG_SQL, "�½���ɹ���");
        } catch (SQLException ex) {
        	ex.printStackTrace();
			Log.i(TAG_SQL, "�½���ʧ�ܣ�");
        }     
    }     
    
    // �������     
    public void insertItem(Position p) { 
        SQLiteDatabase db = dbHelper.getWritableDatabase();     
             
        try {     
            String sql = "INSERT INTO " + TABLE_NAME + "(PHONE, TIME, LON, LAT, ANGLE) VALUES ('"
            	+ p.getUser() + "', '" + p.getTime() + "' , "+ p.getLon() +", "+ p.getLat() +","+ p.getAngle() +")";     
            // execSQL() - ִ��ָ���� sql     
            db.execSQL(sql);  
			Log.i(TAG_SQL, "�������ɹ���");        
        } catch (SQLException ex) {
        	ex.printStackTrace();
			Log.i(TAG_SQL, "�������ʧ�ܣ�");
        }     
    }     
    
    // ɾ�����     
    public void deleteAllItems() {    
        try {     
            SQLiteDatabase db = dbHelper.getWritableDatabase();     
            db.delete(TABLE_NAME, " id < 9999999", null);
			Log.i(TAG_SQL, "ɾ�����б���ɹ���");
        } catch (SQLException e) {     
        	e.printStackTrace();
			Log.i(TAG_SQL, "ɾ�����б���ʧ�ܣ�");
        }     
    }     
    
    // �������     
    public void updateItem() {    
    }     

    // ��ѯ���     
    public List<Position> showItems() { 
        SQLiteDatabase db = dbHelper.getReadableDatabase();     

    	List<Position> positions = new ArrayList<Position>();
        try {     
            String[] column = { "id", "phone", "time", "lon", "lat", "angle" };     
            Cursor cursor = db.query(TABLE_NAME, column, null, null, null,     
                    null, null);      
            cursor.moveToFirst();     
    
            while (cursor.getPosition() != cursor.getCount()) {
            	String phone = cursor.getString(1);
            	Timestamp time = Timestamp.valueOf(cursor.getString(2));
            	float lon = Float.parseFloat(cursor.getString(3));
            	float lat = Float.parseFloat(cursor.getString(4));
            	float angle = Float.parseFloat(cursor.getString(5));
            	Position position = new Position(phone, time, lon, lat, angle);
            	positions.add(position);     
                cursor.moveToNext();     
    			Log.i(TAG_SQL, position.toString());
            }     
            cursor.close();
        } catch (SQLException ex) {     
        	ex.printStackTrace();
			Log.i(TAG_SQL, "��ѯ���б���ʧ�ܣ�");
        }     
        return positions;
    }     
    
    // ɾ����ݱ�     
    public void dropTable() {      
        SQLiteDatabase db = dbHelper.getWritableDatabase();     
        String sql = "DROP TABLE IF EXISTS " + TABLE_NAME;     
        try {     
            db.execSQL(sql); 
			Log.i(TAG_SQL, "ɾ���ɹ���");       
        } catch (SQLException ex) {     
        	ex.printStackTrace();
			Log.i(TAG_SQL, "ɾ���ʧ�ܣ�");
        }     
    }        
} 